<?php
/**
 * Created by Zhy
 * User: Zhy
 * Date: 2019/4/1
 * Time: 16:02
 */

namespace app\admin\controller;


use think\Db;
use think\Request;

class School extends Base
{
    /**
     * 学校首页展示
     * @return mixed|\think\response\Json
     */
    public function index(){
        if(request()->isAjax ()){
            $params = input('param.');
            $where = [];
            //认证学生数
            if(isset($params['user_start']) && $params['user_start'] != '' && isset($params['user_end']) && $params['user_end'] != ''){
                $where['school_student_num'] = ['between', [$params['user_start'], $params['user_end']]];
            }
            if(isset($params['user_start']) && $params['user_start'] != '' && isset($params['user_end']) && $params['user_end'] == ''){
                $where['school_student_num'] = ['>=', $params['user_start']];
            }
            if(isset($params['user_start']) && $params['user_start'] == '' && isset($params['user_end']) && $params['user_end'] != ''){
                $where['school_student_num'] = ['<=', $params['user_end']];
            }
            
            //日期
            if(isset($params['add_start']) && $params['add_start'] != '' && isset($params['add_end']) && $params['add_end'] != ''){
                $where['school_time'] = ['between', [$params['add_start'], $params['add_end']]];
            }
            if(isset($params['add_start']) && $params['add_start'] != '' && isset($params['add_end']) && $params['add_end'] == ''){
                $where['school_time'] = ['>=', $params['add_start']];
            }if(isset($params['add_start']) && $params['add_start'] == '' && isset($params['add_end']) && $params['add_end'] != ''){
                $where['school_time'] = ['<=', $params['add_end']];
            }
            
            //年级数
            if(isset($params['grade_start']) && $params['grade_start'] != '' && isset($params['grade_end']) && $params['grade_end'] != ''){
                $where['school_grade_num'] = ['between', [$params['grade_start'], $params['grade_end']]];
            }
            if(isset($params['grade_start']) && $params['grade_start'] != '' && isset($params['grade_end']) && $params['grade_end'] == ''){
                $where['school_grade_num'] = ['>=', $params['grade_start']];
            }
            if(isset($params['grade_start']) && $params['grade_start'] == '' && isset($params['grade_end']) && $params['grade_end'] != ''){
                $where['school_grade_num'] = ['<=', $params['grade_end']];
            }
            
            //班级数
            if(isset($params['class_start']) && $params['class_start'] != '' && isset($params['class_end']) && $params['class_end'] != ''){
                $where['school_class_num'] = ['between', [$params['class_start'], $params['class_end']]];
            }
            if(isset($params['class_start']) && $params['class_start'] != '' && isset($params['class_end']) && $params['class_end'] == ''){
                $where['school_class_num'] = ['>=', $params['class_start']];
            }
            if(isset($params['class_start']) && $params['class_start'] == '' && isset($params['class_end']) && $params['class_end'] != ''){
                $where['school_class_num'] = ['<=', $params['class_end']];
            }
            
            //学校名
            if(isset($params['school_name']) && $params['school_name'] != ''){
                $where['school_name'] = ['like', '%' . $params['school_name'] . '%'];
            }
            
            //状态
            if(isset($params['school_status']) && $params['school_status'] != ''){
                $where['school_status'] = $params['school_status'];
            }
            $where['school_del'] = 1;
            $Nowpage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            $count = Db::name('school')->where($where)->count();// 获取总条数
            $lists = Db::name('school')
                ->field('school_id, school_name, school_status, school_time, school_student_num, 
                school_grade_num, school_class_num, school_update_time')
                ->where($where)
                ->page($Nowpage,$limits)
                ->order('school_time desc, school_id desc')
                ->select();
            $lists = $this->getTongjiInfo($lists,1);
            return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
        }
        if(session('?school_id')){
            session('school_id',null);
        }
        return $this->fetch();
    }
    
    /**
     * 添加学校
     * @return mixed|\think\response\Json
     */
    public function schoolAdd(){
        if(Request::instance()->isPost()){
            $school_name = input('school_name');
            $province = input('province');
            $city = input('city');
            $district = input('district');
            if($province =='' || $city == '' || $district == ''){
                return json(['code' => 100, 'msg' => '请选择地区']);
            }
            $data = [
                'school_name' => $school_name,
                'school_time' => date('Y-m-d H:i:s'),
                'school_update_time' => date('Y-m-d H:i:s'),
                'school_sheng' => $province,
                'school_shi' => $city,
                'school_qu' => $district
            ];
            $res = Db::table('up_school')->insert($data);
            if($res){
                return json(['code' => 200, 'msg' => '添加成功']);
            }else{
                return json(['code' => 100, 'msg' => '添加失败']);
            }
        }
        $province = Db::table('up_area')->where('level', 1)->select();
        $this->assign('province', $province);
        return $this->fetch('school/schooladd');
    }
    
    /**
     * 编辑学校
     * @return mixed|\think\response\Json
     */
    public function schoolEdit(){
        if(Request::instance()->isPost()){
            $school_id = input('school_id');
            $school_name = input('school_name');
            $province = input('province');
            $city = input('city');
            $district = input('district');
            if($province =='' || $city == '' || $district == ''){
                return json(['code' => 100, 'msg' => '请选择地区']);
            }
            $data = [
                'school_name' => $school_name,
                'school_update_time' => date('Y-m-d H:i:s'),
                'school_sheng' => $province,
                'school_shi' => $city,
                'school_qu' => $district
            ];
            $res = Db::table('up_school')->where('school_id', $school_id)->update($data);
            if($res){
                return json(['code' => 200, 'msg' => '编辑成功']);
            }else{
                return json(['code' => 100, 'msg' => '编辑失败']);
            }
        }
        $id = input('school_id');
        $schoolInfo = Db::table('up_school')->where('school_id', $id)->find();
        $province = Db::table('up_area')->where('level', 1)->select();
        $city = Db::table('up_area')->where('pid', $schoolInfo['school_sheng'])->select();
        $district = Db::table('up_area')->where('pid', $schoolInfo['school_shi'])->select();
        $this->assign([
           'school_id' => $id,
            'info' => $schoolInfo,
            'province' => $province,
            'city' => $city,
            'district' => $district
        ]);
        return $this->fetch('school/schooledit');
    }
    
    /**
     * 学校详情
     * @return mixed
     */
    public function schoolDetail(){
        $school_id = input('school_id');
        //基本信息
        $info = Db::table('up_school')
            ->join('up_area ua1', 'school_sheng = ua1.district_id', 'left')
            ->join('up_area ua2', 'school_shi = ua2.district_id', 'left')
            ->join('up_area ua3', 'school_qu = ua3.district_id', 'left')
            ->field('school_id, school_name, ua1.district province, ua2.district city, 
            ua3.district, school_student_num, school_grade_num, school_class_num')
            ->where('school_id', $school_id)
            ->find();
        //学校组织
        $grade_class = Db::table('up_relevance')
            ->field('re_grade_id, re_class_id')
            ->where('re_school_id', $school_id)
            ->select();
        foreach($grade_class as $k => $v){
            $grade = Db::table('up_grade')->where('grade_id', $v['re_grade_id'])->value('grade_name');
            $class = Db::table('up_class')->where('class_id', $v['re_class_id'])->value('class_name');
            $grade_class[$k]['name'] = $grade . $class;
        }
        $info['sun'] = $grade_class;
        $this->assign('info', $info);
        return $this->fetch('school/schooldetail');
    }
    
    /**
     * 批量删除学校
     * @return \think\response\Json
     */
    public function schoolDel(){
        $ids = input('ids');
        if($ids == ''){
            return json(['code' => 100, 'msg' => '请选择要删除的学校']);
        }
        //用户表里的信息
        $res = Db::table('up_u_user')->where('user_school', 'IN', $ids)->select();
        //用户关联学校/年级/班级表的信息
        $res1 = Db::table('up_grade_class')->where('gs_school_id', 'IN', $ids)->select();
        if($res || $res1){
            return json(['code' => 100, 'msg' => '请先移除该学校下的用户']);
        }
        $result = Db::table('up_school')->where('school_id', 'IN', $ids)->setField('school_del', 2);
        if($result){
            return json(['code' => '200', 'msg' => '删除成功']);
        }else{
            return json(['code' => 100, 'msg' => '删除失败']);
        }
    }
    
    /**
     * 批量修改状态
     * @return \think\response\Json
     */
    public function schoolStatus(){
        $ids = input('ids');
        $num = input('num');
        if($ids == ''){
            return json(['code' => 100, 'msg' => '请选择学校']);
        }
        $status = Db::table('up_school')->where('school_id', 'IN', $ids)->column('school_status');
        if($num == 1){//上架
            if(in_array(1, $status)){
                return json(['code' => 100, 'msg' => '所选学校中含有上架中学校，请重新选择']);
            }
            $msg = '上架成功';
        }else{//下架
            if(in_array(2, $status)){
                return json(['code' => 100, 'msg' => '所选学校中含有已下架学校，请重新选择']);
            }
            $msg = '下架成功';
        }
        $res = Db::table('up_school')->where('school_id', 'IN', $ids)->setField('school_status', $num);
        if($res){
            return json(['code' => 200, 'msg' => $msg]);
        }else{
            return json(['code' => 100, 'msg' => '操作失败']);
        }
    }
    
    /**
     * 年级列表
     * @return mixed|\think\response\Json
     */
    public function schoolGrade(){
        if(request()->isAjax ()){
            $params = input('param.');
            $Nowpage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            $where['re_school_id'] = $params['school_id'];
            $count = Db::name('relevance')
                ->join('up_grade', 're_grade_id = grade_id', 'left')
                ->where($where)
                ->group('re_grade_id')
                ->count();// 获取总条数
            $lists = Db::name('relevance')
                ->join('up_grade', 're_grade_id = grade_id', 'left')
                ->field('grade_name, re_time, re_update_time, re_school_id, re_grade_id, re_class_id')
                ->where($where)
                ->page($Nowpage,$limits)
//                ->order('re_time asc')
                ->group('re_grade_id')
                ->select();
            //班级数
            foreach($lists as $k => $v){
                $count1 = Db::table('up_relevance')
                    ->where('re_school_id', $v['re_school_id'])
                    ->where('re_grade_id', $v['re_grade_id'])
                    ->count();
                $lists[$k]['class_num'] = $count1;
            }
            $lists = $this->getTongjiInfo($lists,2);
            return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
        }
        $id = input('school_id');
        if($id != ''){
            session('school_id',$id);
        }
        if(session('?grade_id')){
            session('grade_id',null);
        }
        return $this->fetch('school/schoolgrade');
    }
    
    /**
     * 添加年级
     * @return mixed|\think\response\Json
     */
    public function gradeAdd(){
        if(Request::instance()->isPost()){
            $grade_id = input('grade_id');
            $class_id = input('class_id');
            if($grade_id == '' || $class_id == '')
                return json(['code' => 100, 'msg' => '请选择必填项']);
            $class_arr = explode(',', $class_id);
            $class_num = count($class_arr);//班级数量
            Db::startTrans();
            try{
                foreach($class_arr as $k => $v){
                    $data = [
                        're_school_id' => session('school_id'),
                        're_grade_id' => $grade_id,
                        're_class_id' => $v,
                        're_time' => date('Y-m-d H:i:s'),
                        're_update_time' => date('Y-m-d H:i:s')
                    ];
                    Db::table('up_relevance')->insert($data);
                }
                //年级数加1
                Db::table('up_school')->where('school_id', session('school_id'))->setInc('school_grade_num');
                //班级数增加添加的数量
                Db::table('up_school')->where('school_id', session('school_id'))->setInc('school_class_num', $class_num);
                Db::commit();
                $res = 1;
            }catch(\Exception $e){
                Db::rollback();
                $res = 0;
            };
            if($res == 1){
                return json(['code' => 200, 'msg' => '添加成功']);
            }else{
                return json(['code' => 100, 'msg' => '添加失败']);
            }
        }
        //年级
        $grade = Db::table('up_grade')
            ->where('grade_status', 1)
            ->where('grade_del', 1)
            ->field('grade_id, grade_name')
            ->select();
        //班级
        $class = Db::table('up_class')
            ->where('class_status', 1)
            ->where('class_del', 1)
            ->field('class_id, class_name')
            ->select();
        $this->assign([
            'grade' => $grade,
            'class' => $class
        ]);
        return $this->fetch('school/gradeadd');
    }
    
    /**
     * 编辑年级
     * @return mixed|\think\response\Json
     */
    public function gradeEdit(){
        if(Request::instance()->isPost()){
            $grade_id = input('grade_id');
            $class_id = input('class_id');
            if($grade_id == '' || $class_id == '')
                return json(['code' => 100, 'msg' => '请选择必填项']);
            $class_arr = explode(',', $class_id);
            $class_num = count($class_arr);//班级数量
            Db::startTrans();
            try{
                //查询原来的班级数
                $num = Db::table('up_relevance')
                    ->where('re_school_id', session('school_id'))
                    ->where('re_grade_id', $grade_id)
                    ->count();
                //扣除原来的班级数量
                Db::table('up_school')->where('school_id', session('school_id'))->setDec('school_class_num', $num);
                //删除该学校该年级下所有班级
                Db::table('up_relevance')
                    ->where('re_school_id', session('school_id'))
                    ->where('re_grade_id', $grade_id)
                    ->delete();
                //重新插入
                foreach($class_arr as $k => $v){
                    $data = [
                        're_school_id' => session('school_id'),
                        're_grade_id' => $grade_id,
                        're_class_id' => $v,
                        're_time' => date('Y-m-d H:i:s'),
                        're_update_time' => date('Y-m-d H:i:s')
                    ];
                    Db::table('up_relevance')->insert($data);
                }
                //班级数增加添加的数量
                Db::table('up_school')->where('school_id', session('school_id'))->setInc('school_class_num', $class_num);
                Db::commit();
                $res = 1;
            }catch(\Exception $e){
                Db::rollback();
                $res = 0;
            };
            if($res == 1){
                return json(['code' => 200, 'msg' => '编辑成功']);
            }else{
                return json(['code' => 100, 'msg' => '编辑失败']);
            }
        }
        $grade_id = input('grade_id');//年级ID
        //班级ID
        $class_id = Db::table('up_relevance')
            ->where('re_school_id', session('school_id'))
            ->where('re_grade_id', $grade_id)
            ->column('re_class_id');
        //年级
        $grade = Db::table('up_grade')
            ->where('grade_status', 1)
            ->where('grade_del', 1)
            ->field('grade_id, grade_name')
            ->select();
        //班级
        $class = Db::table('up_class')
            ->where('class_status', 1)
            ->where('class_del', 1)
            ->field('class_id, class_name')
            ->select();
        $this->assign([
            'grade' => $grade,
            'class' => $class,
            'grade_id' => $grade_id,
            'class_id' => implode(',', $class_id)
        ]);
        return $this->fetch('school/gradeedit');
    }
    
    /**
     * 删除年级---暂时没有次功能
     * @return \think\response\Json
     */
    public function gradeDel(){
        $grade_id = input('id');
        $res = Db::table('up_grade_class')
            ->where('gs_school_id', session('school_id'))
            ->where('gs_grade_id', $grade_id)
            ->find();
        $res1 = Db::table('up_relevance')
            ->where('re_school_id', session('school_id'))
            ->where('re_grade_id', $grade_id)
            ->find();
        if($res || $res1){
            return json(['code' => 100, 'msg' => '请先移除该年级下内容']);
        }
        $class_num = Db::table('up_relevance')
            ->where('re_school_id', session('school_id'))
            ->where('re_grade_id', $grade_id)
            ->count();
        Db::startTrans();
        try{
            //年级数减1
            Db::table('up_school')->where('school_id', session('school_id'))->setDec('school_grade_num');
            //班级数减掉删除的数量
            Db::table('up_school')->where('school_id', session('school_id'))->setDec('school_class_num', $class_num);
            Db::commit();
            $result = 1;
        }catch(\Exception $e){
            Db::rollback();
            $result = 0;
        }
        if($result == 1){
            return json(['code' => 200, 'msg' => '删除成功']);
        }else{
            return json(['code' => 100, 'msg' => '删除失败']);
        }
    }
    
    /**
     * 班级列表
     * @return mixed|\think\response\Json
     */
    public function schoolClass(){
        if(request()->isAjax ()){
            $params = input('param.');
            $Nowpage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            $where['re_school_id'] = session('school_id');
            $where['re_grade_id'] = $params['grade_id'];
            $count = Db::name('relevance')->where($where)->count();// 获取总条数
            $lists = Db::name('relevance')
                ->join('up_class', 're_class_id = class_id', 'left')
                ->field('class_name, re_time, re_update_time, re_school_id, re_grade_id, re_class_id')
                ->where($where)
                ->page($Nowpage,$limits)
//                ->order('re_time asc')
                ->select();
            //认证人数
            foreach($lists as $k => $v){
                $count1 = Db::table('up_grade_class')
                    ->where('gs_school_id', $v['re_school_id'])
                    ->where('gs_grade_id', $v['re_grade_id'])
                    ->where('gs_class_id', $v['re_class_id'])
//                    ->where('gs_role', 1)
                    ->count();
                $lists[$k]['student_num'] = $count1;
            }
            $lists = $this->getTongjiInfo($lists,3);
            return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
        }
        $id = input('grade_id');
        if($id != ''){
            session('grade_id',$id);
        }
        if(session('?class_id')){
            session('class_id', null);
        }
        return $this->fetch('school/schoolclass');
    }
    
    /**
     * 班级成员列表
     * @return mixed|\think\response\Json
     */
    public function schoolStudent(){
        if(request()->isAjax ()){
            $params = input('param.');
            $Nowpage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            $where['gs_school_id'] = session('school_id');
            $where['gs_grade_id'] = session('grade_id');
            $where['gs_class_id'] = $params['class_id'];
            $count = Db::name('grade_class')->where($where)->count();// 获取总条数
            $lists = Db::name('grade_class')
                ->join('up_u_user', 'gs_user_id = user_id', 'left')
                ->field('user_id, user_username, user_time, user_gender')
                ->where($where)
                ->page($Nowpage,$limits)
                ->order('user_time asc')
                ->select();
            $lists = $this->getTongjiInfo($lists,4);
            return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
        }
        $id = input('class_id');
        if($id != ''){
            session('class_id',$id);
        }
        return $this->fetch('school/schoolstudent');
    }
    
    /**
     * 导入Excel
     * @return mixed
     */
    public function excelIn(){
        set_time_limit(0);
        if(request()->file('file')){
            $file = request()->file('file');
            $info  = $file->getInfo();
            $file_path = $info['tmp_name'];
            //取出文件后缀
            $file_type = explode(".",$info['name']);
            $file_type = end($file_type);
            vendor("PHPExcel.PHPExcel.IOFactory");
            if( $file_type == 'xls' ){
                $inputFileType = 'Excel5';    //这个是读 xls的
            }else{
                $inputFileType = 'Excel2007';//这个是计xlsx的
            }
            $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
            $objPHPExcel = $objReader->load($file_path);
            $objWorksheet = $objPHPExcel->getActiveSheet();
            $highestRow = $objWorksheet->getHighestRow();
//            print_r($highestRow);die;
            $time = date('Y-m-d H:i:s');
            Db::startTrans();
            try{
                for ($i=2;$i<=$highestRow;$i++) {
                    $a = $objPHPExcel->getActiveSheet ()->getCell ("A" . $i)->getValue ();//获取A列的值
                    $b = $objPHPExcel->getActiveSheet ()->getCell ("B" . $i)->getValue ();//获取B列的值
                    $c = $objPHPExcel->getActiveSheet ()->getCell ("C" . $i)->getValue ();//获取C列的值
                    $d = $objPHPExcel->getActiveSheet ()->getCell ("D" . $i)->getValue ();//获取D列的值
                    $e = $objPHPExcel->getActiveSheet ()->getCell ("E" . $i)->getValue ();//获取E列的值
                    $a = trim($a, ' ');
//                    $b = trim($b, ' ');
//                    $c = trim($c, ' ');
                    $d = trim($d, ' ');
                    $e = trim($e, ' ');
                    if($a != '' && $d != '' && $e != ''){
                        $shi = Db::table('up_area')->where('district_id', $d)->value('pid');//区ID
                        $sheng = Db::table('up_area')->where('district_id', $shi)->value('pid');//省ID
                        //插入学校
                        $data = [
                            'school_name' => $a,//学校名称
                            'school_time' => $time,
                            'school_update_time' => $time,
                            'school_sheng' => $sheng,//省
                            'school_shi' => $shi,//市
                            'school_qu' => $d,//区
                        ];

                        //---查重start---
                        $double = Db::table('up_school')
                            ->where('school_sheng',$sheng)
                            ->where('school_shi',$shi)
                            ->where('school_qu',$d)
                            ->where('school_del',1)
                            ->where('school_name',$a)
                            ->find();
                        if(!$double){
                            $school_id = Db::table('up_school')->insertGetId($data);
                            //计算班级数
                            $grade_class = explode(',', $e);
                            $class_num = count($grade_class);
                            //计算年级数
                            $grade = [];
                            foreach($grade_class as $k => $v){
                                //组装年级数组
                                $sun = explode('-', $v);
                                array_push($grade, $sun[0]);
                                //插入关联表
                                $insert = [
                                    're_school_id' => $school_id,
                                    're_grade_id' => $sun[0],
                                    're_class_id' => $sun[1],
                                    're_time' => $time,
                                    're_update_time' => $time
                                ];
                                Db::table('up_relevance')->insert($insert);
                            }
                            $grade = array_unique($grade);
                            $grade_num = count($grade);
                            //更新年级和班级数量
                            $updata = [
                                'school_grade_num' => $grade_num,
                                'school_class_num' => $class_num
                            ];
                            Db::table('up_school')->where('school_id', $school_id)->update($updata);
                        }
                        //----查重end----


                    }
                }
                Db::commit();
                echo '/admin/School/index';
            }catch(\Exception $e){
                Db::rollback();
            }
            
        }
        return $this->fetch('school/excelin');
    }
    
    /**
     * 批量转移
     * @return \think\response\Json
     */
    public function shiftAll(){
        $ids = input('ids');
        if($ids == ''){
            return json(['code' => 100, 'msg' => '请选择需要转移的成员']);
        }
        return json(['code' => 200, 'data' => $ids, 'msg' => '']);
    }
    
    /**
     * 批量转移用户
     * @return mixed|\think\response\Json
     */
    public function doShiftAll(){
        if(Request::instance()->isPost()){
            $ids = input('ids');
            $school_id = input('school_id');
            $grade_id = input('grade_id');
            $class_id = input('class_id');
            $is_have = Db::table('up_relevance')
                ->where('re_school_id', $school_id)
                ->where('re_grade_id', $grade_id)
                ->where('re_class_id', $class_id)
                ->find();
            if(!$is_have){
                return json(['code' => 100, 'msg' => '所选班级不存在,请重新选择']);
            }
            $ids_arr = explode(',', $ids);
            $ids_num = count($ids_arr);
            Db::startTrans();
            try{
                //修改user表中的学校
                Db::table('up_u_user')->where('user_id', 'IN', $ids)->setField('user_school', $school_id);
                //减掉原学校人数
                Db::table('up_school')->where('school_id', session('school_id'))->setDec('school_student_num', $ids_num);
                //增加现在学校人数
                Db::table('up_school')->where('school_id', $school_id)->setInc('school_student_num', $ids_num);
                //修改关联表中的内容
                Db::table('up_grade_class')
                    ->where('gs_school_id', session('school_id'))
                    ->where('gs_grade_id', session('grade_id'))
                    ->where('gs_class_id', session('class_id'))
                    ->where('gs_user_id', 'IN', $ids)
                    ->update([
                        'gs_school_id' => $school_id,
                        'gs_grade_id' => $grade_id,
                        'gs_class_id' => $class_id
                    ]);
                Db::commit();
                $result = 1;
            }catch(\Exception $e){
                Db::rollback();
                $result = 0;
            }
            if($result == 1){
                return json(['code' => 200, 'msg' => '转移成功']);
            }else{
                return json(['code' => 100, 'msg' => '转移失败']);
            }
        }
        $ids = input('ids');
        //所有学校
        $school = Db::table('up_school')
            ->field('school_id, school_name')
            ->where('school_id', 'neq', session('school_id'))
            ->where('school_status', 1)
            ->where('school_del', 1)
            ->select();
        //年级
        $grade = Db::table('up_grade')
            ->field('grade_id, grade_name')
            ->where('grade_status', 1)
            ->where('grade_del', 1)
            ->select();
        //班级
        $class = Db::table('up_class')
            ->field('class_id, class_name')
            ->where('class_id', 'neq', session('class_id'))
            ->where('class_status', 1)
            ->where('class_del', 1)
            ->select();
        $this->assign([
            'ids' => $ids,
            'school' => $school,
            'grade' => $grade,
            'class' => $class,
            'grade_id' => session('grade_id')
        ]);
        return $this->fetch('school/doshiftall');
    }

    /**
     * 获取统计数据（统一的）内部使用
     * @param $lists 原始数据
     * @param $type 类型（1代表学校  2代表年级  3代表班级  4代表人）
     * @return mixed
     */
    protected function getTongjiInfo($lists,$type)
    {
        if($lists){
            foreach ($lists as $k=>$v){
                $where = array();
                $where['user_type'] = 1;
                if($type == 1){
                    $where['user_school'] = $v['school_id'];
                }
                if($type == 2){
                    $where['user_school'] = $v['re_school_id'];
                    $where['user_grade'] = $v['re_grade_id'];
                }
                if($type == 3){
                    $where['user_school'] = $v['re_school_id'];
                    $where['user_grade'] = $v['re_grade_id'];
                    $where['user_class'] = $v['re_class_id'];
                }
                if($type == 4){
                    $where['rbp_user_id'] = $v['user_id'];
                }
                //1、闯关通关书数量
                $lists[$k]['allpass'] = Db::table('up_reading_book_pass')->alias('bp')
                    ->join('up_u_user u', 'bp.rbp_user_id = u.user_id', 'left')
                    ->where($where)
                    ->count();
                //2、闯关中书数量
                $lists[$k]['alling'] = Db::table('up_reading_book_ing')->alias('ing')
                    ->join('up_u_user u', 'ing.rbp_user_id = u.user_id', 'left')
                    ->where($where)
                    ->count();
                //3、十个类型的通关数量
                $types = [
                    '全部',
                    '绘本读物', '科普百科', '名人励志', '诗歌散文', '经典名著', '传统文化',
                    '科幻冒险', '童话寓言', '动物文学', '习作美文'
                ];
                //3、各类型闯关通关书数量
                for($i=1;$i<=10;$i++){
                    $lists[$k]['alltypes' . $i] = Db::table('up_reading_book_pass')->alias('bp')
                        ->join('up_u_user u', 'bp.rbp_user_id = u.user_id', 'left')
                        ->join('up_reading_book b', 'bp.rbp_book_id = b.rb_id', 'left')
                        ->where($where)
                        ->where('rb_book_type_name',$types[$i])
                        ->count();
                }
            }
        }
        return $lists;
    }

    /**
     * 批量导出
     * @return \think\response\Json
     */
    public function excelTongjis(){
        set_time_limit(0);
        $ids = input('ids');
        if($ids == ''){
            return json(['code' => 100, 'msg' => '请选择学校']);
        }
        if(input('tongji_start') && input('tongji_end')){
            $start = input('tongji_start');
            $end = input('tongji_end');
            if($start >= $end){
                return json(['code' => 100, 'msg' => '结束日期必须大于开始日期']);
            }
        }else{
            return json(['code' => 100, 'msg' => '请选择统计日期']);
        }
        $id = explode(',',$ids)[0];
//        $id = 18;
        $field = 'user_time,user_id,user_username,user_class,user_grade,school_name';
        $field .= ',ua3.district as qu,ua2.district as shi,ua1.district as sheng';
        $lists = Db::table('up_u_user')->alias('u')
            ->join('up_school s', 'u.user_school = s.school_id', 'left')
            ->join('up_area ua1', 's.school_sheng = ua1.district_id', 'left')
            ->join('up_area ua2', 's.school_shi = ua2.district_id', 'left')
            ->join('up_area ua3', 's.school_qu = ua3.district_id', 'left')
            ->where('user_type',1)
            ->where('user_school',$id)
            ->where('user_status',1)
            ->field($field)
            ->select();
        if($lists){
            //3、十个类型的通关数量
            $types = [
                '全部',
                '绘本读物', '科普百科', '名人励志', '诗歌散文', '经典名著', '传统文化',
                '科幻冒险', '童话寓言', '动物文学', '习作美文'
            ];
            $where = array();
            $where['rbp_date'] = ['between time',"$start,$end"];
            foreach($lists as $k => $v){
                $lists[$k]['user_time'] = $start . '~' . $end;
//
                $where['rbp_user_id'] = $v['user_id'];
                //1、闯关通关书数量
                $lists[$k]['allpass'] = Db::table('up_reading_book_pass')->alias('bp')
                    ->join('up_u_user u', 'bp.rbp_user_id = u.user_id', 'left')
                    ->where($where)
                    ->count();
                //2、闯关中书数量
                $lists[$k]['alling'] = Db::table('up_reading_book_ing')->alias('ing')
                    ->join('up_u_user u', 'ing.rbp_user_id = u.user_id', 'left')
                    ->where($where)
                    ->count();
                //3、各类型闯关通关书数量
                for($i=1;$i<=10;$i++){
                    $lists[$k]['alltypes' . $i] = Db::table('up_reading_book_pass')->alias('bp')
                        ->join('up_u_user u', 'bp.rbp_user_id = u.user_id', 'left')
                        ->join('up_reading_book b', 'bp.rbp_book_id = b.rb_id', 'left')
                        ->where($where)
                        ->where('rb_book_type_name',$types[$i])
                        ->count();
                }
            }
            $cellname = [
                ['user_time','统计时间',25,'LEFT'],
                ['user_id','学生ID号',15,'LEFT'],
                ['user_username','学生姓名',20,'LEFT'],
                ['user_class','班级',10,'LEFT'],
                ['user_grade','年级',10,'LEFT'],
                ['school_name','学校',20,'LEFT'],
                ['qu','区',10,'LEFT'],
                ['shi','市',10,'LEFT'],
                ['sheng','省',10,'LEFT'],
                ['allpass','通关书籍数',15,'LEFT'],
                ['alling','闯关中书籍数',15,'LEFT'],
                ['alltypes1',$types[1].'通关数',15,'LEFT'],
                ['alltypes2',$types[2].'通关数',15,'LEFT'],
                ['alltypes3',$types[3].'通关数',15,'LEFT'],
                ['alltypes4',$types[4].'通关数',15,'LEFT'],
                ['alltypes5',$types[5].'通关数',15,'LEFT'],
                ['alltypes6',$types[6].'通关数',15,'LEFT'],
                ['alltypes7',$types[7].'通关数',15,'LEFT'],
                ['alltypes8',$types[8].'通关数',15,'LEFT'],
                ['alltypes9',$types[9].'通关数',15,'LEFT'],
                ['alltypes10',$types[10].'通关数',15,'LEFT'],
            ];
            $res = exportExcel('学校学生闯关情况统计','TJ',$cellname,$lists);
            return json($res);
        }else{
            return json(['code' => 100, 'msg' => '此学校暂无学生数据']);
        }

    }
}